SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC [dbo].[SP_TASALD_EMPR_Q17]
/*---------------------------------------------------------------------*/
/*----- Empresa          : OFISIS S.A.                            -----*/
/*----- Cliente          : OFISIS S.A.                         	  -----*/
/*----- Sistema          : Contabilidad                           -----*/
/*----- Modulo           : Empresa                                -----*/
/*----- Programa         : Reporte de ajuste por inflacion        -----*/
/*----- Nombre           : SP_TASALD_EMPR_Q17                	  -----*/
/*----- Script           : TASALD_EMPR                            -----*/
/*----- Desarrollado     : Betsy Sanchez Arenas                   -----*/
/*----- Fecha            : 11/10/1997                             -----*/
/*----- Base de Datos    : MS SQL Server                          -----*/
/*----- Version BD       : 8.0									  -----*/
/*----- Invoca a SP      :                                        -----*/
/*---------------------------------------------------------------------*/
/*---------------------------------------------------------------------*/
/*----- Modificado 1 por : Martin Garcia Farfan                   -----*/
/*----- Fecha Modifica   : 15/04/1999                             -----*/
/*----- Detalle Modifica : Estandarizacion                        -----*/
/*---------------------------------------------------------------------*/
/*---------------------------------------------------------------------*/
/*----- Modificado 2 por : Martin Garcia Farfan                   -----*/
/*----- Fecha Modifica   : 27/01/2000                             -----*/
/*----- Detalle Modifica : Longitud de Auxiliar                   -----*/
/*---------------------------------------------------------------------*/
/*---------------------------------------------------------------------*/
/*----- Modificado 3 por : Marco Acu?a Paredes                    -----*/
/*----- Fecha Modifica   : 03/10/2000                             -----*/
/*----- Detalle Modifica : Nueva Versi?n                          -----*/
/*---------------------------------------------------------------------*/
/*----- Comentarios      : REPORTE AUXILIAR - AUX. DOCUMENTO      -----*/
/*-----    SOLO MOSTRARA SALDOS PENDIENTES, ACTUALIZARA           -----*/
/*-----	   LAS CUENTAS DEUDORAS SI SU SALDO ES MAYOR A            -----*/
/*-----	   CERO(0) Y ACREEDORAs SI SU SALDO ES MENOR A            -----*/
/*-----	   CERO(0) - EXPRESADO EN NUEVOS SOLES                    -----*/
/*---------------------------------------------------------------------*/
/*---------------------------------------------------------------------*/
/*----- Drop Proc SP_TASALD_EMPR_Q17                              -----*/
/*----- Grant Execute On SP_TASALD_EMPR_Q17 To PUBLIC             -----*/
/*----- SP_TASALD_EMPR_Q17 '01', 1, 2001, 2, '9259020000', 'N', 'D', 'M'                                 	  -----*/
/*---------------------------------------------------------------------*/
@ISCO_EMPR           TD_VC_002,
@INNU_CNTB_EMPR      TD_IN_001_CONT,
@INNU_ANNO           TD_IN_004_ANNO,
@INNU_MESE           TD_IN_002_MESE,
@ISCO_CNTA           TD_VC_020_CNTA,
@ISST_CNTA_NATU      TD_VC_001_STAT,
@ISTI_REPO           TD_VC_001,
@ISTI_SALD           TD_VC_001
as
Create Table #TWSALD_EMPR_Q17_1
(TI_AUXI_EMPR char(1)   null,
 CO_AUXI      varchar(20)  null,
 NO_AUXI      Char(50)  null,
 TI_DOCU      Char(3)   null,
 NU_DOCU      Char(20)  null,
 NU_PERI      Char(7)   null,
 HT_CARG      Numeric(16,4)     null,
 HT_ABON      Numeric(16,4)     null,
 FA_ACTU      Numeric(9,4)     null,
 AT_CARG     Numeric(16,4)     null,
 AT_ABON      Numeric(16,4)     null,
 VA_REI       Numeric(16,4)     null)
Declare 
 @CSTI_AUXI_EMPR TD_VC_001, 
 @CSCO_AUXI      TD_VC_020, 
 @CSNO_AUXI      TD_VC_050, 
 @CSTI_DOCU      TD_VC_003, 
 @CSNU_DOCU 	 TD_VC_020, 
 @CNNU_ANNO      TD_IN_004_ANNO,  @CNNU_MESE      TD_IN_002_MESE,
 @VSNU_PERI      Char(7), 
 @CNNU_SALD      TD_NU_016_004,
 @VNHT_CARG      TD_NU_016_004,  
 @VNHT_ABON      TD_NU_016_004,  
 @VNFA_ACTU      TD_NU_009_004,
 @VNAT_CARG      TD_NU_016_004,
 @VNAT_ABON      TD_NU_016_004,    
 @VNVA_REIN      TD_NU_016_004,
 @VNNU_IND1      TD_NU_009_004,
 @VNNU_IND2      TD_NU_009_004
 
/* inicializo variables  */
Select  @CNNU_SALD = 0, 
	@VNHT_CARG = 0,
	@VNHT_ABON = 0,
	@VNFA_ACTU = 0,
	@VNAT_CARG = 0,
	@VNAT_ABON = 0,
	@VNVA_REIN = 0,
	@VNNU_IND1 = 0.00,
	@VNNU_IND2 = 0.00
/* obtengo indice de actualizacion a mes actual */
Select @VNNU_IND2 = IN_ACTU
From   TTINDI_ACTU   
Where NU_ANNO = @INNU_ANNO 
And NU_MESE = @INNU_MESE

If @VNNU_IND2 IS null or @VNNU_IND2 = 0.00
 Select @VNNU_IND2 = 1.00

/* declaro Cursor para obtener el importe de lAs ctAs para cada periodo */
If rtrim(@ISTI_REPO) = 'D'
 /* tipo de reporte detallado */
 Begin /* Si el reporte es detallado   */
  Declare CU_TASALD_EMPR_Q17_1 Cursor
   for
  Select  t1.TI_AUXI_EMPR, t1.CO_AUXI_EMPR, max(t2.NO_AUXI), t1.TI_DOCU, t1.NU_DOCU, max(t1.NU_ANNO), max(t1.NU_MESE),
  	  Isnull(SUM( ((ASCII(TI_OPER)/67)* Isnull(IM_MVTO_CNTB, 0))), 0) - Isnull(SUM(((65/ASCII(TI_OPER))* Isnull(IM_MVTO_CNTB, 0))), 0)
   From TXMVTO_CNTB t1
     Left Outer Join TMAUXI_EMPR t2
     On t1.CO_EMPR = t2.CO_EMPR
     And  t1.TI_AUXI_EMPR = t2.TI_AUXI_EMPR
     And  t1.CO_AUXI_EMPR = t2.CO_AUXI_EMPR
   Where t1.CO_EMPR      = @ISCO_EMPR
    And  t1.NU_CNTB_EMPR = @INNU_CNTB_EMPR
    And  t1.NU_ANNO      = @INNU_ANNO
    And  t1.NU_MESE      <= @INNU_MESE
    And  t1.CO_CNTA_EMPR = rtrim(@ISCO_CNTA)
    And  t1.SI_MVTO_CNTB = 'APR'
   Group By t1.TI_AUXI_EMPR, t1.CO_AUXI_EMPR, t1.TI_DOCU, t1.NU_DOCU
   Order By t1.TI_AUXI_EMPR, t1.CO_AUXI_EMPR, t1.TI_DOCU, t1.NU_DOCU   

 Open CU_TASALD_EMPR_Q17_1
 Fetch CU_TASALD_EMPR_Q17_1 Into @CSTI_AUXI_EMPR, @CSCO_AUXI, @CSNO_AUXI, @CSTI_DOCU,
	@CSNU_DOCU,@CNNU_ANNO, @CNNU_MESE, @CNNU_SALD  

While @@Fetch_status = 0
 Begin /* Fin de Cursor  */
  If @CNNU_SALD != 0.00
   Begin /* Si el saldo != a cero */
    /* obtengo indice de actualizacion a mes historico */
    Select @VNNU_IND1 = isnull(IN_ACTU,1)
    From   TTINDI_ACTU   
    Where NU_ANNO = @CNNU_ANNO 
    And NU_MESE = @CNNU_MESE

   if @CNNU_MESE = 0
    Select @VNNU_IND1 = isnull(IN_ACTU,1)
     From   TTINDI_ACTU         Where NU_ANNO = @CNNU_ANNO  - 1
     And NU_MESE = 12
 
  
   If @VNNU_IND1 IS null or @VNNU_IND1 = 0.00
    Select @VNNU_IND1 = 1.00
    Select @VNFA_ACTU = @VNNU_IND2 / @VNNU_IND1
    Select @VNFA_ACTU = round(@VNFA_ACTU,3 )

   If @CNNU_SALD > 0.00
    Begin /* si el saldo es mayor a cero */
     Select @VNHT_CARG = round(@CNNU_SALD,2)
     Select @VNHT_ABON = 0.00
    If @ISST_CNTA_NATU = 'S'
     Begin /* si la naturaleza S=deudora y el saldo es mayor a cero */
      Select @VNAT_CARG = @VNHT_CARG * @VNFA_ACTU
      Select @VNAT_CARG = Round(@VNAT_CARG,2)
      Select @VNAT_ABON = 0.00
      Select @VNVA_REIN  = @VNAT_CARG - @VNHT_CARG
     End /* si la naturaleza S=deudora y el saldo es mayor a cero */
    Else
     Begin /* si la naturaleza N=acreedora y el saldo es mayor a cero */
      Select @VNAT_CARG = @VNHT_CARG * @VNFA_ACTU
      Select @VNAT_CARG = Round(@VNAT_CARG,2)
      Select @VNAT_ABON = 0.00
      Select @VNVA_REIN  = @VNAT_CARG - @VNHT_CARG                       
     End /* si la naturaleza N=acreedora y el saldo es mayor a cero */
   End /* si el saldo es mayor a cero */

   If @CNNU_SALD < 0.00
    Begin /* si el saldo es menor a cero */
     Select @VNHT_CARG = 0.00
     Select @VNHT_ABON = round(abs(@CNNU_SALD),2)
     If @ISST_CNTA_NATU = 'N'
      Begin /* si la naturaleza N=acreedora y el saldo es menor a cero */
       Select @VNAT_ABON = @VNHT_ABON * @VNFA_ACTU
       Select @VNAT_ABON = Round(@VNAT_ABON,2)
       Select @VNAT_CARG = 0.00
       Select @VNVA_REIN  = @VNAT_ABON - @VNHT_ABON
      End /* si la naturaleza N=acreedora y el saldo es menor a cero */
     Else
      Begin /* si la naturaleza S=deudora y el saldo es menor a cero */
       Select @VNAT_ABON = @VNHT_ABON * @VNFA_ACTU
       Select @VNAT_ABON = Round(@VNAT_ABON,2)
       Select @VNAT_CARG = 0.00        Select @VNVA_REIN  = @VNAT_ABON - @VNHT_ABON                          
      End /* si la naturaleza S=deudora y el saldo es menor a cero */
    End /* si el saldo es menor a cero */

    Select @VSNU_PERI = convert(char(4),@CNNU_ANNO) + '-' + SubString(convert(char(3),@CNNU_MESE+100),2,3)
    If @ISTI_SALD = 'M' 
      Begin
       Insert Into #TWSALD_EMPR_Q17_1 Values(@CSTI_AUXI_EMPR,
          @CSCO_AUXI, @CSNO_AUXI, @CSTI_DOCU, @CSNU_DOCU,
          @VSNU_PERI, @VNHT_CARG, @VNHT_ABON, @VNFA_ACTU,
          @VNAT_CARG, @VNAT_ABON, @VNVA_REIN ) 
      End
     Else If @ISTI_SALD = 'E' And @VNHT_CARG > 0.00
      Begin
        Insert Into #TWSALD_EMPR_Q17_1 Values(@CSTI_AUXI_EMPR,
         @CSCO_AUXI, @CSNO_AUXI, @CSTI_DOCU, @CSNU_DOCU,
         @VSNU_PERI, @VNHT_CARG, @VNHT_ABON, @VNFA_ACTU,
         @VNAT_CARG, @VNAT_ABON, @VNVA_REIN ) 
      End
     Else If @ISTI_SALD = 'C' And @VNHT_ABON > 0.00
      Begin
       Insert Into #TWSALD_EMPR_Q17_1 Values(@CSTI_AUXI_EMPR,
         @CSCO_AUXI, @CSNO_AUXI, @CSTI_DOCU, @CSNU_DOCU,
         @VSNU_PERI, @VNHT_CARG, @VNHT_ABON, @VNFA_ACTU,
         @VNAT_CARG, @VNAT_ABON, @VNVA_REIN ) 
      End
     End /* Si el saldo != a cero */
    
    Fetch CU_TASALD_EMPR_Q17_1 Into @CSTI_AUXI_EMPR, @CSCO_AUXI, @CSNO_AUXI, @CSTI_DOCU,
       @CSNU_DOCU, @CNNU_ANNO, @CNNU_MESE, @CNNU_SALD            
  End /* Fin de Cursor  */
  Close CU_TASALD_EMPR_Q17_1
  Deallocate  CU_TASALD_EMPR_Q17_1
 
 Select TI_AUXI_EMPR, CO_AUXI, NO_AUXI, TI_DOCU,
        NU_DOCU, NU_PERI,  HT_CARG,  HT_ABON,FA_ACTU, AT_CARG, AT_ABON, VA_REI
 From #TWSALD_EMPR_Q17_1
 Return  
End /* Si el reporte es detallado   */
 Else If rtrim(@ISTI_REPO) = 'R'
 /* tipo de reporte resumido */
 Begin
   Declare CU_TASALD_EMPR_Q17_2 Cursor
    for
     Select t1.TI_AUXI_EMPR, t1.CO_AUXI_EMPR, max(t2.NO_AUXI), max(t1.NU_ANNO), max(t1.NU_MESE), 
	 (Isnull(sum(Isnull(t1.IM_CARG_CNTB, 0)), 0) - Isnull(sum(Isnull(t1.IM_ABON_CNTB, 0)), 0))
     From TASALD_EMPR t1
	   Left Outer Join TMAUXI_EMPR t2
	   On t1.CO_EMPR = t2.CO_EMPR
	   And t1.TI_AUXI_EMPR = t2.TI_AUXI_EMPR
	   And t1.CO_AUXI_EMPR = t2.CO_AUXI_EMPR
     Where t1.CO_EMPR    = @ISCO_EMPR
     And  t1.NU_CNTB_EMPR = @INNU_CNTB_EMPR
     And  t1.NU_ANNO      = @INNU_ANNO
     And  t1.NU_MESE      <= @INNU_MESE
     And  t1.CO_CNTA_EMPR = rtrim(@ISCO_CNTA)
     And  t1.TI_AUXI_EMPR IS NOT NULL
     Group By t1.TI_AUXI_EMPR, t1.CO_AUXI_EMPR 
     Order By t1.TI_AUXI_EMPR, t1.CO_AUXI_EMPR 
 
    Open CU_TASALD_EMPR_Q17_2
    Fetch CU_TASALD_EMPR_Q17_2 Into @CSTI_AUXI_EMPR, @CSCO_AUXI, @CSNO_AUXI, @CNNU_ANNO, 
	@CNNU_MESE, @CNNU_SALD  
  
   While @@Fetch_status = 0
    Begin
     If @CNNU_SALD != 0.00
      Begin
       /* obtengo indice de actualizacion a mes historico */
       Select @VNNU_IND1 = isnull(IN_ACTU,1)
       From   TTINDI_ACTU  
       Where NU_ANNO = @CNNU_ANNO
       And NU_MESE = @CNNU_MESE

       if @CNNU_MESE = 0
         Select @VNNU_IND1 = isnull(IN_ACTU,1)
         From   TTINDI_ACTU  
         Where NU_ANNO = @CNNU_ANNO - 1
         And NU_MESE = 12
       If @VNNU_IND1 IS null or @VNNU_IND1 = 0.00
         Select @VNNU_IND1 = 1.00

       Select @VNFA_ACTU = @VNNU_IND2 / @VNNU_IND1
       Select @VNFA_ACTU = round(@VNFA_ACTU,3 )

      If @CNNU_SALD > 0.00
       Begin
         Select @VNHT_CARG = round(@CNNU_SALD,2)
         Select @VNHT_ABON = 0.00
        If @ISST_CNTA_NATU = 'S'
         Begin
          Select @VNAT_CARG = @VNHT_CARG * @VNFA_ACTU
          Select @VNAT_CARG = Round(@VNAT_CARG,2)
          Select @VNAT_ABON = 0.00
          Select @VNVA_REIN  = @VNAT_CARG - @VNHT_CARG
         End
        Else
         Begin
          Select @VNAT_CARG = @VNHT_CARG * @VNFA_ACTU
          Select @VNAT_CARG = Round(@VNAT_CARG,2)
          Select @VNAT_ABON = 0.00
          Select @VNVA_REIN  = @VNAT_CARG - @VNHT_CARG                       
         End
        End
       If @CNNU_SALD < 0.00
        Begin
          Select @VNHT_CARG = 0.00
          Select @VNHT_ABON = round(abs(@CNNU_SALD),2)
          If @ISST_CNTA_NATU = 'N'
           Begin
            Select @VNAT_ABON = @VNHT_ABON * @VNFA_ACTU
            Select @VNAT_ABON = Round(@VNAT_ABON,2)
            Select @VNAT_CARG = 0.00
            Select @VNVA_REIN  = @VNAT_ABON - @VNHT_ABON
           End
          Else
           Begin
            Select @VNAT_ABON = @VNHT_ABON * @VNFA_ACTU
            Select @VNAT_ABON = Round(@VNAT_ABON,2)
            Select @VNAT_CARG = 0.00
            Select @VNVA_REIN  = @VNAT_ABON - @VNHT_ABON                         
          End
        End
       Select @VSNU_PERI = convert(char(4),@CNNU_ANNO) + '-' +
        SubString(convert(char(3),@CNNU_MESE+100),2,3)
       If @ISTI_SALD = 'M' 
        Begin
          Insert Into #TWSALD_EMPR_Q17_1 Values(@CSTI_AUXI_EMPR, @CSCO_AUXI, @CSNO_AUXI, 
			null, null, @VSNU_PERI, @VNHT_CARG, @VNHT_ABON, @VNFA_ACTU, @VNAT_CARG, 
			@VNAT_ABON, @VNVA_REIN ) 
        End
      Else If @ISTI_SALD = 'E' And @VNHT_CARG > 0.00
       Begin
          Insert Into #TWSALD_EMPR_Q17_1 Values(@CSTI_AUXI_EMPR, @CSCO_AUXI, @CSNO_AUXI, 
			null, null, @VSNU_PERI, @VNHT_CARG, @VNHT_ABON, @VNFA_ACTU, @VNAT_CARG, 
			@VNAT_ABON, @VNVA_REIN ) 
       End
      Else If @ISTI_SALD = 'C' And @VNHT_ABON > 0.00
       Begin
          Insert Into #TWSALD_EMPR_Q17_1 Values(@CSTI_AUXI_EMPR,
            @CSCO_AUXI, @CSNO_AUXI, null, null,
            @VSNU_PERI, @VNHT_CARG, @VNHT_ABON, @VNFA_ACTU,
            @VNAT_CARG, @VNAT_ABON, @VNVA_REIN ) 
       End
     End
   
    Fetch CU_TASALD_EMPR_Q17_2 Into @CSTI_AUXI_EMPR, @CSCO_AUXI, @CSNO_AUXI, 
         @CNNU_ANNO, @CNNU_MESE, @CNNU_SALD            
   End
  
  Close CU_TASALD_EMPR_Q17_2
  Deallocate  CU_TASALD_EMPR_Q17_2
  Select TI_AUXI_EMPR, CO_AUXI, NO_AUXI, NU_PERI,  HT_CARG,  HT_ABON,
  FA_ACTU, AT_CARG, AT_ABON, VA_REI
  From #TWSALD_EMPR_Q17_1
  Return  
 End
/*--------------------------------------- Fin ---------------------------------*/


GO
